﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using CRMFramework;
using System.Collections;
using Newtonsoft.Json;
using CRMEntity;

namespace CRMBiz
{
    public static class ApplicationProcessHandler
    {

        public static DataSet BindApplicationProcess(string type, string currentdate, int pageIndex, int pageSize)
        {
            DataSet ds = null;
            StringBuilder sqlqry = null;
            try
            {
                if (type == "PQ")
                {
                    var sbWhere = new StringBuilder(" where crm_loaninfo.app_Date >= '2010-10-01' and crm_loaninfo.app_check=0 and crm_contact.NumberFromAppinfo=1 and crm_loaninfo.app_status='Manual Respond'");
                    sqlqry = new StringBuilder("select crm_contact.*, crm_loaninfo.* from crm_loaninfo inner join crm_contact on crm_contact.app_id=crm_loaninfo.app_id ")
                                            .Append(sbWhere)
                                            .Append(" order by app_ApplyWithSomeoneWhoOwns DESC, app_LoanAmount DESC, app_Date ASC")
                                            .Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize)
                                            .Append(";")
                                            .Append("SELECT COUNT(*) FROM crm_contact ") // Get total
                                            .Append("INNER JOIN crm_loaninfo ON crm_loaninfo.app_id=crm_contact.app_id")
                                            .Append(sbWhere);
                    ds = DBhelper.ExecuteDataset(sqlqry.ToString());
                }
                else if (type == "MR")
                {
                    var sbWhere = new StringBuilder(" where crm_loaninfo.app_Date = '" + Convert.ToDateTime(currentdate).ToString("yyyy-MM-dd") + "' and crm_contact.NumberFromAppinfo=1 and crm_loaninfo.app_status='Manual Respond'");
                    sqlqry = new StringBuilder("select crm_contact.*, crm_loaninfo.* from crm_loaninfo inner join crm_contact on crm_contact.app_id=crm_loaninfo.app_id ")
                                            .Append(sbWhere)
                                            .Append(" order by crm_loaninfo.app_id desc");
                    ds = DBhelper.ExecuteDataset(sqlqry.ToString());
                }
                else if (type == "AR")
                {
                    var sbWhere = " where crm_loaninfo.app_Date = '" + Convert.ToDateTime(currentdate).ToString("yyyy-MM-dd") + "' and crm_contact.NumberFromAppinfo=1 and crm_loaninfo.app_status!='Manual Respond' and crm_loaninfo.app_status!='' and crm_loaninfo.app_status!='Not Eligible' and crm_loaninfo.app_status!='Withdrawn'";
                    sqlqry = new StringBuilder("select crm_contact.*, crm_loaninfo.* from crm_loaninfo inner join crm_contact on crm_contact.app_id=crm_loaninfo.app_id ")
                                            .Append(sbWhere)
                                            .Append(" order by crm_loaninfo.app_id desc");
                    ds = DBhelper.ExecuteDataset(sqlqry.ToString());
                }
                else if (type == "QS")
                {

                }
            }
            catch (Exception)
            {
                throw;
            }
            return ds;
        }

        public static bool UpdateStatusApplicationProcess(int app_id)
        {
            bool flag = false;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@app_id",  app_id)
                };
                string sbSql = "update crm_loaninfo set app_check=1 where app_id=@app_id";
                DBhelper.ExecuteNonQuery(sbSql, para);
                flag = true;
            }
            catch (Exception)
            {
                throw;
            }
            return flag;
        }
    }
}
